"""
__project__ = 'PythonProject'
__file_name__ = 'class_16'
__author__ = 'Sun'
__time__ = '2021/4/22 10:27'
__product_name = PyCharm

"""

import pymysql
def create_table():
    # 创建数据库连接
    db = pymysql.connect(host="49.233.39.160", user="user", password="leboAa!#$123",
                         database="lebo16", port=3306, charset="utf8")
    # 获取游标
    cursor = db.cursor()
    # 如果表存在就删除，不存在就创建
    cursor.execute("drop table if exists students")
    # 创建学生表
    sql = """create table students(
    studentNo int unsigned primary key auto_increment,
    name varchar(20),
    sex varchar(20) not null,
    hometown varchar(20),
    age int unsigned,
    class varchar(20),
    card varchar(20))"""
    # 捕获异常
    try:
        cursor.execute(sql)
        print("数据表创建成功")
    except Exception as e:
        print("数据库创建失败原因：%s" % e)
    finally:
        # 关闭游标，关闭数据库连接
        cursor.close()
        db.close()

def insert_table():
    # 连接数据库
    db = pymysql.connect(host="49.233.39.160", user="user", password="leboAa!#$123",
                         database="lebo16", port=3306, charset="utf8")
    # 获取游标
    cursor = db.cursor()
    # 向表中插入数据
    sql = """insert into students values
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655')
"""
    # 捕获异常
    try:
        cursor.execute(sql)
        db.commit()
        print("数据插入成功")
    except Exception as msg:
        print("数据插入失败，原因是：%s" % msg)
        db.rollback()
    # 关闭游标，关闭数据库连接
    finally:
        cursor.close()
        db.close()

create_table()
insert_table()
